﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using OrderManage.Model;
using OrderManage.Utility;

namespace OrderManage.DAL
{
    public class UserDAL : DbHelperBase
    {
        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="qcmodel"></param>
        /// <returns></returns>
        public UserInfo Login(UserQcInfo qcmodel)
        {
            var model = new UserInfo();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT UserId, UserName, Password, Role, FullName FROM dt_User WHERE UserName=@UserName ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@UserName", qcmodel.UserName);
                    using (var reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["UserId"] != DBNull.Value)
                                model.UserId = int.Parse(reader["UserId"].ToString());
                            model.UserName = reader["UserName"] == obj ? null : Convert.ToString(reader["UserName"]);
                            model.Password = reader["Password"] == obj ? null : Convert.ToString(reader["Password"]);
                            if (reader["Role"] != DBNull.Value)
                                model.Role = int.Parse(reader["Role"].ToString());
                            model.FullName = reader["FullName"] == obj ? null : Convert.ToString(reader["FullName"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddUser(UserInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [dt_User]([UserName],[Password],[Role],[FullName],[Gender],[IDNO],[PhoneNo],[Email],[QQ]) ");
            strSql.Append(" VALUES(@UserName,@Password,@Role,@FullName,@Gender,@IDNO,@PhoneNo,@Email,@QQ) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@UserName", model.UserName);
                    Db.AddInParameter(cmd, "@Password", EncryptPassWord.EncryptString(model.Password));
                    Db.AddInParameter(cmd, "@Role", model.Role);
                    Db.AddInParameter(cmd, "@FullName", model.FullName);
                    Db.AddInParameter(cmd, "@Gender", model.Gender);
                    Db.AddInParameter(cmd, "@IDNO", model.IDNO);
                    Db.AddInParameter(cmd, "@PhoneNo", model.PhoneNo);
                    Db.AddInParameter(cmd, "@Email", model.Email);
                    Db.AddInParameter(cmd, "@QQ", model.QQ);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool ModifyPwd(UserInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [dt_User] SET Password=@Password WHERE UserName = @UserName ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Password", EncryptPassWord.EncryptString(model.Password));
                    Db.AddInParameter(cmd, "@UserName", model.UserName);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool ModifyUser(UserInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [dt_User] SET [Role] = @Role,[FullName] = @FullName,[Gender] = @Gender,[IDNO] = @IDNO,[PhoneNo] = @PhoneNo,[Email] = @Email,[QQ] = @QQ WHERE UserId=@UserId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Role", model.Role);
                    Db.AddInParameter(cmd, "@FullName", model.FullName);
                    Db.AddInParameter(cmd, "@Gender", model.Gender);
                    Db.AddInParameter(cmd, "@IDNO", model.IDNO);
                    Db.AddInParameter(cmd, "@PhoneNo", model.PhoneNo);
                    Db.AddInParameter(cmd, "@Email", model.Email);
                    Db.AddInParameter(cmd, "@QQ", model.QQ);
                    Db.AddInParameter(cmd, "@UserId", model.UserId);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool RemoveUser(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [dt_User] WHERE UserId=@UserId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@UserId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public int GetTotal(UserQcInfo qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append("select  count(*) AS Total FROM [dt_User] WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.UserName))
            {
                strSql.Append(" AND UserName LIKE '%" + qcmodel.UserName + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.FullName))
            {
                strSql.Append(" AND FullName LIKE '%" + qcmodel.FullName + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.Role))
            {
                strSql.Append(" AND Role = @Role ");
            }
            if (!string.IsNullOrEmpty(qcmodel.Gender))
            {
                strSql.Append(" AND Gender = @Gender ");
            }
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.Role))
                    {
                        Db.AddInParameter(cmd, "@Role", qcmodel.Role);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.Gender))
                    {
                        Db.AddInParameter(cmd, "@Gender", qcmodel.Gender);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<UserInfo> GetUserList(UserQcInfo qcmodel)
        {
            var list = new List<UserInfo>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            int start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            int end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by InputTime) AS idx,[UserId],[UserName],[Password],[Role],[FullName],[Gender],[IDNO],[PhoneNo],[Email],[QQ],[InputTime] FROM [dt_User] where 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.UserName))
            {
                strSql.Append(" AND UserName LIKE '%" + qcmodel.UserName + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.FullName))
            {
                strSql.Append(" AND FullName LIKE '%" + qcmodel.FullName + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.Role))
            {
                strSql.Append(" AND Role = @Role ");
            }
            if (!string.IsNullOrEmpty(qcmodel.Gender))
            {
                strSql.Append(" AND Gender = @Gender ");
            }
            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new UserInfo();
                            if (reader["UserId"] != DBNull.Value)
                                model.UserId = Convert.ToInt32(reader["UserId"].ToString());
                            model.UserName = reader["UserName"] == obj ? null : Convert.ToString(reader["UserName"]);
                            model.Password = reader["Password"] == obj
                                                 ? null
                                                 : EncryptPassWord.DecryptString(reader["Password"].ToString());
                            if (reader["Role"] != DBNull.Value)
                                model.Role = Convert.ToInt32(reader["Role"].ToString());
                            model.FullName = reader["FullName"] == obj ? null : Convert.ToString(reader["FullName"]);
                            if (reader["Gender"] != DBNull.Value)
                                model.Gender = Convert.ToInt32(reader["Gender"].ToString());
                            model.IDNO = reader["IDNO"] == obj ? null : Convert.ToString(reader["IDNO"]);
                            model.PhoneNo = reader["PhoneNo"] == obj ? null : Convert.ToString(reader["PhoneNo"]);
                            model.Email = reader["Email"] == obj ? null : Convert.ToString(reader["Email"]);
                            model.QQ = reader["QQ"] == obj ? null : Convert.ToString(reader["QQ"]);
                            if (reader["InputTime"] != DBNull.Value)
                                model.InputTime = Convert.ToDateTime(reader["InputTime"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public UserInfo GetUserInfo(string id)
        {
            var model = new UserInfo();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [UserId],[UserName],[Password],[Role],[FullName],[Gender],[IDNO],[PhoneNo],[Email],[QQ],[InputTime] FROM [dt_User]  WHERE UserId=@UserId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@UserId", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["UserId"] != DBNull.Value)
                                model.UserId = Convert.ToInt32(reader["UserId"].ToString());
                            model.UserName = reader["UserName"] == obj ? null : Convert.ToString(reader["UserName"]);
                            if (reader["Role"] != DBNull.Value)
                                model.Role = Convert.ToInt32(reader["Role"].ToString());
                            model.FullName = reader["FullName"] == obj ? null : Convert.ToString(reader["FullName"]);
                            if (reader["Gender"] != DBNull.Value)
                                model.Gender = Convert.ToInt32(reader["Gender"].ToString());
                            model.IDNO = reader["IDNO"] == obj ? null : Convert.ToString(reader["IDNO"]);
                            model.PhoneNo = reader["PhoneNo"] == obj ? null : Convert.ToString(reader["PhoneNo"]);
                            model.Email = reader["Email"] == obj ? null : Convert.ToString(reader["Email"]);
                            model.QQ = reader["QQ"] == obj ? null : Convert.ToString(reader["QQ"]);
                            if (reader["InputTime"] != DBNull.Value)
                                model.InputTime = Convert.ToDateTime(reader["InputTime"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public bool UserNameValidation(string userName)
        {
            var strSql = new StringBuilder();
            strSql.Append("select count(*) AS Total FROM [dt_User] WHERE UserName=@UserName ");
            var result = false;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@UserName", userName);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (Convert.ToInt32(reader["Total"]) > 0)
                            {
                                result = true;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return result;
            }
        }

        public bool PasswordValidation(string userName, string password)
        {
            var strSql = new StringBuilder();
            strSql.Append("select count(*) AS Total FROM [dt_User] WHERE UserName=@UserName AND Password=@Password ");
            var result = false;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@UserName", userName);
                    Db.AddInParameter(cmd, "@Password", EncryptPassWord.EncryptString(password));
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (Convert.ToInt32(reader["Total"]) > 0)
                            {
                                result = true;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return result;
            }
        }
    }
}
